import java.sql.*;
import java.util.Scanner;

public class LoginExperiment {
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://127.0.0.1/android_database"; //choose database
    static final String USER = "root";
    static final String PASSWORD = "225107";

    public static void main(String[] args){
        Connection conn = null;
        Statement stmt = null;
        try{
            Class.forName(JDBC_DRIVER);
            System.out.println("connecting to DB...");
            conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);

            System.out.println("Statement...");
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT Sno, Sname, Smajor, Sclass FROM student"; //choose table
            ResultSet rs = stmt.executeQuery(sql);

//            while(rs.next()){
//                String id = rs.getString("Sno");
//                String name = rs.getString("Sname");
//                String major = rs.getString("Smajor");
//                String mclass = rs.getString("Sclass");
//
//                System.out.println("StudentID:"+id);
//                System.out.println("name:"+name);
//                System.out.println("major:"+major);
//                System.out.println("class:"+mclass);
//                System.out.println("\n");
//            }

//            String myID = "2018300485";
            System.out.println("please input the ID number you'd like to refer:");
            Scanner scan = new Scanner(System.in);
            String myID = scan.nextLine();

            while(rs.next()){
                String TestID = rs.getString("Sno");
                String TestName = rs.getString("Sname");
                String TestMajor = rs.getString("Smajor");
                String TestClass = rs.getString("Sclass");
                if (TestID.equals(myID)){
                    System.out.println("the result is:");
                    System.out.println("ID:"+TestID);
                    System.out.println("name:"+TestName);
                    System.out.println("major:"+TestMajor);
                    System.out.println("class:"+TestClass);
                }
//                if (!rs.next()){
//                    System.out.println("No such a student");
//                }

            }

            rs.close();
            stmt.close();
            conn.close();
        }
        catch(SQLException se){
            se.printStackTrace();
        }
        catch(Exception e){
            e.printStackTrace();
        }
        finally {
            try{
                if (stmt != null) stmt.close();
            }catch(SQLException se){}
            try{
                if (conn != null) conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
        System.out.println("That's all, DB is well connected");
        //mScan();
    }

    public static void mScan(){
        System.out.println("please input Student Number...");
        Scanner scan = new Scanner(System.in);
        String str_id = scan.nextLine();
        System.out.println("ID user_input is:" + str_id);
    }

}
